BigQueryでGAになったCONTAINS_SUBSTRで正規化検索を試す（全半角カタカナ・記号）

BigQueryのCONTAINS_SUBSTR()関数の正規化機能で、全角/半角カタカナや記号がどのように評価されるのか試してみる。

#Google BigQuery

#小ネタ

#Google Cloud (GCP)

ikeda

2021.07.20

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

データアナリティクス事業本部、池田です。
BigQueryの2021/07/19のリリースでCONTAINS_SUBSTRという関数が generally available（GA）になったので、動かしてみました。
【 Release notes 】
【 CONTAINS_SUBSTR 】　※執筆時点で日本語未対応

どんな関数か？

以下は先述の公式ドキュメントの例そのままなのですが、 第一引数の中に、第二引数の値（検索値）が含まれるかをtrue/falseで評価してくれます。

SELECT CONTAINS_SUBSTR('the blue house', 'Blue house') AS result;

+--------+
| result |
+--------+
| true   |
+--------+

その際には、大文字/小文字は区別しません。

便利そうなポイントとして、第一引数にテーブルやカラムを指定することができます。
↓テーブルを検索する場合

WITH Recipes AS
 (SELECT 'Blueberry pancakes' as Breakfast, 'Egg salad sandwich' as Lunch, 'Potato dumplings' as Dinner UNION ALL
  SELECT 'Potato pancakes', 'Toasted cheese sandwich', 'Beef stroganoff' UNION ALL
  SELECT 'Ham scramble', 'Steak avocado salad', 'Tomato pasta' UNION ALL
  SELECT 'Avocado toast', 'Tomato soup', 'Blueberry salmon' UNION ALL
  SELECT 'Corned beef hash', 'Lentil potato soup', 'Glazed ham')
SELECT * FROM Recipes WHERE CONTAINS_SUBSTR(Recipes, 'toast');

+-------------------+-------------------------+------------------+
| Breakfast         | Lunch                   | Dinner           |
+-------------------+-------------------------+------------------+
| Potato pancakes   | Toasted cheese sandwich | Beef stroganoff  |
| Avocado toast     | Tomato soup             | Blueberry samon  |
+-------------------+-------------------------+------------------+

（献立（テーブル）の中から、どこかにトーストを含む1日（レコード）を抽出するイメージですかね。）

↓カラムを指定して検索する場合

WITH Recipes AS
 (SELECT 'Blueberry pancakes' as Breakfast, 'Egg salad sandwich' as Lunch, 'Potato dumplings' as Dinner UNION ALL
  SELECT 'Potato pancakes', 'Toasted cheese sandwich', 'Beef stroganoff' UNION ALL
  SELECT 'Ham scramble', 'Steak avocado salad', 'Tomato pasta' UNION ALL
  SELECT 'Avocado toast', 'Tomato soup', 'Blueberry salmon' UNION ALL
  SELECT 'Corned beef hash', 'Lentil potato soup', 'Glazed ham')
SELECT * FROM Recipes WHERE CONTAINS_SUBSTR((Lunch, Dinner), 'potato');

+-------------------+-------------------------+------------------+
| Breakfast         | Lunch                   | Dinner           |
+-------------------+-------------------------+------------------+
| Bluberry pancakes | Egg salad sandwich      | Potato dumplings |
| Corned beef hash  | Lentil potato soup      | Glazed ham       |
+-------------------+-------------------------+------------------+

（こちらは昼食と夕食（指定のカラム）のいずれかに、芋を含む1日を抽出するイメージですね。）

私が気になったのは値を評価する際の「正規化」です。

Before values are compared, they are normalized and case folded with NFKC normalization.

NFKCという手法で正規化してから評価してくれるそうです。

次のような例が掲載されていました。

SELECT '\u2168 day' AS a, 'IX' AS b, CONTAINS_SUBSTR('\u2168', 'IX') AS result;

+----------------------+
| a      | b  | result |
+----------------------+
| Ⅸ day | IX | true   |
+----------------------+

\u2168 というのは Ⅸ の記号のことなので、以下のように書いても同じ結果（true）です。
SELECT 'Ⅸ day' AS a, 'IX' AS b, CONTAINS_SUBSTR('Ⅸ day', 'IX') AS result;

日本語を扱っていると、全半角のカタカナの統一など、めんどうに感じることがあります。その辺にどのくらい対応しているのか次章で試してみました。

全角/半角カタカナや記号の正規化を試してみる

さっそく試してみた結果です。（結果は執筆時点のもの。）
※ハイライトしている行がtrueを返したものです。

SELECT CONTAINS_SUBSTR('the blue house', 'Blue house') AS result; -- 例そのまま：true
SELECT CONTAINS_SUBSTR('the ｂｌｕｅ house', 'Blue house') AS result; -- 全角blue：true
SELECT CONTAINS_SUBSTR('the ｂｌｕｅ　ｈｏｕｓｅ', 'Blue house') AS result; -- 全角blue+全角スペース+全角house：true
SELECT CONTAINS_SUBSTR('the bluё house', 'Blue house') AS result; -- ロシア語のe：false
SELECT CONTAINS_SUBSTR('the bluЁ house', 'Bluё house') AS result; -- ロシア語のeとE：true

全半角アルファベットや全半角スペースはうまく正規化してくれました。
まあ、確かに「e」とロシア語の「ё」は別ものですよね。

SELECT CONTAINS_SUBSTR('全角ハンバーグ丼', 'ハンバーグ') AS result; -- そのまま：true
SELECT CONTAINS_SUBSTR('半角ﾊﾝﾊﾞｰｸﾞ丼', 'ハンバーグ') AS result; -- 半角カタカナ：true
SELECT CONTAINS_SUBSTR('半角ﾊﾝﾊｰｸ丼', 'ハンバーグ') AS result; -- 半角カタカナ濁点無し：false
SELECT CONTAINS_SUBSTR('ひらがなはんばーぐ丼', 'ハンバーグ') AS result; -- ひらがな：false
SELECT CONTAINS_SUBSTR('チルダハンバ～グ丼', 'ハンバーグ') AS result; -- 全角チルダ：false
SELECT CONTAINS_SUBSTR('チルダハンバ~グ丼', 'ハンバ～グ') AS result; -- 半角と全角のチルダ：true
SELECT CONTAINS_SUBSTR('ハイフン記号ハンバ―グ丼', 'ハンバーグ') AS result; -- 記号のハイフン：false
SELECT CONTAINS_SUBSTR('㊤ハンバーグ丼', '上ハンバーグ') AS result; -- ㊤と上：true
SELECT CONTAINS_SUBSTR('↑ハンバーグ丼', '上ハンバーグ') AS result; -- ↑と上：false
SELECT CONTAINS_SUBSTR('㊕ハンバーグ丼', '特ハンバーグ') AS result; -- ㊕と特：true
SELECT CONTAINS_SUBSTR('（おすすめ）ハンバーグ丼', '(おすすめ)ハンバーグ') AS result; -- 全角と半角の括弧：true

カタカナの全角/半角はうまく評価しれくれるみたいですね。
記号系が予想外。すごい…

↓そして、日本の記号でも特に愛されてやまない株式会社。

SELECT CONTAINS_SUBSTR('㊑ハンバーグ丼', '株ハンバーグ') AS result; -- まるカブ：true
SELECT CONTAINS_SUBSTR('㈱ハンバーグ丼', '（株）ハンバーグ') AS result; -- 括弧カブ（全角検索）：true
SELECT CONTAINS_SUBSTR('㈱ハンバーグ丼', '(株)ハンバーグ') AS result; -- 括弧カブ（半角検索）：true
SELECT CONTAINS_SUBSTR('㍿ハンバーグ丼', '株式会社ハンバーグ') AS result; -- 4文字を1文字で：true

↑に記載のパターンは全てtrue評価！

ちなみに似たような NORMALIZE_AND_CASEFOLD という関数は前からあったみたいですね… （公式ドキュメント）